use axum::Json;
use com::{ code, query, sea_orm::connect_db };
use sea_orm::{query::*, DbBackend };

pub async fn save_field(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    //
    let field =  query::str(&body,"Field")?;
    
    let is_null =  query::bool(&body,"Null")?;
    let def =  query::str_op(&body,"Default")?;
    let key =  query::str_op(&body,"Key")?.unwrap_or_default();
    let auto_increment =  query::bool(&body,"auto_increment")?;
    let extra =  query::str_op(&body,"Extra")?;
    let comment =  query::str_op(&body,"Comment")?;
    let site =  query::str(&body,"site")?;
    let site_name =  query::str_op(&body,"site_name")?.unwrap_or_default();
    // let virtua =  query::bool(&body,"virtual")?;
    
    //
    let typ = get_type(&body)?;
    //
    let is_null  = if is_null { "".to_owned() } else { " not null".to_owned() };
    let auto_increment  = if auto_increment { " auto_increment"} else { "" };
    // let virtua  = if virtua { " virtual" } else { "" };
    let def  = if let Some(f) = def { format!(" default {}", f) } else { "".to_owned() };
    // let def  = if let Some(f) = def { 
    //     if f.contains("(") { format!(" default({})", f) } else {  format!(" default {}", f) }
    // } else { "".to_owned() };
    let comment  = if let Some(f) = comment { format!(" comment '{}'", f) } else { "".to_owned() };
    let extra  = if let Some(f) = extra { format!(" {}", f) } else { "".to_owned() };
    let extra = if extra.contains("auto_increment") { extra.replace("auto_increment", "") } else { extra };
    let key  = if key == "UNI" { " unique" } else if key=="PRI" { " primary key" } else { "" };
    let site = if site == "first" { format!(" {}", site) } else if site == "after" { format!(" after {}", site_name) } else { "".to_owned() };
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    let way = if let Some(_) = query::str_op(&body,"id")?{ "modify" } else { "add" };
    //
    let conn = connect_db(&dbname).await?;
    let sql = format!(
        "alter table {} {} column {} {}{}{}{}{}{}{}{}", 
        table_name,
        way,
        field,
        typ,
        is_null,
        auto_increment,
        key,
        def,
        extra,
		comment,
		site
    );
    let stmt = Statement::from_string(
        DbBackend::MySql, 
        sql
    );
    code::send_ok( conn.execute(stmt).await )
}
pub async fn del_field(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    //
    let field =  query::vec_str(&body,"Field")?;
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    if field.len() > 1 {
        for v in field {
            let sql = format!(
                "alter table {} drop {}", 
                table_name, 
                v,
            );
            let stmt = Statement::from_string(
                DbBackend::MySql, 
                sql
            );
            _ = conn.execute(stmt).await;
        }
        code::ok()
    }else{
        let sql = format!(
            "alter table {} drop {}", 
            table_name, 
            field.join(""),
        );
        let stmt = Statement::from_string(
            DbBackend::MySql, 
            sql
        );
        code::send_ok( conn.execute(stmt).await )
    }
    
}
pub async fn mod_field_name(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    //
    let field =  query::str(&body,"Field")?;
    let typ =  query::str(&body,"Type")?;
    let name =  query::str(&body,"name")?;
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    let sql = format!(
        "alter table {} change {} {} {}",
        table_name,
        field,
        name,
        typ
    );
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn mod_type(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    //
    let field =  query::str(&body,"Field")?;
    let typ = get_type(&body)?;
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    let sql = format!(
        "alter table {} modify {} {}",
        table_name,
        field,
        typ
    );
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn mod_null(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let field =  query::str(&body,"Field")?;
    let typ =  query::str(&body,"Type")?;
    let nul = query::bool(&body,"Null")?;
    let nul = if nul {"null"} else { "not null"};
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    let sql = format!(
        "alter table {} modify {} {} {}",
        table_name,
        field,
        typ,
        nul
    );
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn mod_default(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let field =  query::str(&body,"Field")?;
    let typ =  query::str(&body,"Type")?;
    let def = query::str_op(&body,"Default")?;
    let def  = if let Some(f) = def { format!("default {}", f) } else { "".to_owned() };
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    let sql = format!(
        // "alter table {} alter {} set default {}",
        "alter table {} modify column {} {} {}",
        table_name,
        field,
        typ,
        def,
    );
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn mod_extra(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let field =  query::str(&body,"Field")?;
    let typ =  query::str(&body,"Type")?;
    let extra =  query::str_op(&body,"Extra")?.unwrap_or_default();
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    let sql = format!(
        // "alter table {} alter {} set default {}",
        "alter table {} modify column {} {} {}",
        table_name,
        field,
        typ,
        extra,
    );
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn mod_comment(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let field =  query::str(&body,"Field")?;
    let typ =  query::str(&body,"Type")?;
    let comment =  query::str_op(&body,"Comment")?.unwrap_or_default();
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    let sql = format!(
        // "alter table {} alter {} set default {}",
        "alter table {} modify column {} {} comment '{}'",
        table_name,
        field,
        typ,
        comment,
    );
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn mod_site(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let field =  query::str(&body,"Field")?;
    let typ =  query::str(&body,"Type")?;
    let site =  query::str(&body,"site")?;
    let site_name =  query::str_op(&body,"site_name")?.unwrap_or_default();
    let site = if site == "first" { site } else if site == "after" { format!("after {}", site_name) } else { "".to_owned() };
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    let sql = format!(
        // "alter table {} alter {} set default {}",
        "alter table {} modify column {} {} {}",
        table_name,
        field,
        typ,
        site
    );
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn mod_key(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let field =  query::str(&body,"Field")?;
    let typ =  query::str(&body,"Type")?;
    let key =  query::str(&body,"Key")?;
    
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    let sql = if key == "UNI" {
        format!("alter table {} modify {} {} unique", table_name, field,typ)
        // format!("alter table {} modify column {} {} {}", table_name, field, typ, key );
    } else if key == "PRI" {
        format!("alter table {} modify {} {} primary key", table_name, field,typ)
    } else if key == "INDEX" {
        format!("alter table {} add index {}({})", table_name, field, field )
    } else if key == "FULL" {
        format!("alter table {} add fulltext {}({})", table_name, field, field )
    } else {
        "".to_owned()
    };
    if sql.is_empty() {
        return code::err(format!("要修改的主键类型【{}】不存在", key));
    }
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn add_key(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let field =  query::vec_str(&body,"Field")?;
    let field  = field.join(",");
    let key =  query::str(&body,"Key")?;
    let index_name =  query::str_op(&body,"index_name")?;
    let index_name = if let Some(f) = index_name { format!(" {}", f) } else { "".to_owned() };
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    let sql  = if key == "UNI" { 
        // format!("alter table {} add unique {}({})", table_name, unique_name, field )
        format!("create unique index{} on {}({})", index_name, table_name, field)
    } else if key=="PRI" { 
        format!("alter table {} add primary key({})", table_name, field )
    } else if key=="INDEX" { 
        format!("create index{} on {}({})", index_name, table_name, field)
    } else if key=="FULL" { 
        format!("alter table {} add fulltext {}({})", table_name, index_name, field )
    } else { "".to_owned() };
    if sql.is_empty() {
        return code::err(format!("要添加的主键类型【{}】不存在", key));
    }
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn del_key(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let key_name =  query::str(&body,"key_name")?;
    // let key_name = key_name.join(",");
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;

    let sql  =  if key_name.to_lowercase().contains("primary") {
        format!(
            "alter table {} drop primary key",
            table_name,
        )
    }else{
        format!( "drop index {} on {}",key_name, table_name)
        // format!("alter table {} drop index {}", table_name, key_name)
    };
    //
    let conn = connect_db(&dbname).await?;
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn add_foreign_key(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let field =  query::vec_str_json(&body,"Field")?;
    let field = field.join(",");
    let foreign_name =  query::str_op(&body,"foreign_name")?;
    let foreign_name = if let Some(f) = foreign_name { format!(" constraint {}", f) } else { "".to_owned() };
    let tab_name =  query::str(&body,"tab_name")?;
    let tab_field =  query::str(&body,"tab_field")?;
    let extra =  query::str_op(&body,"extra")?;
    let extra = if let Some(f) = extra { format!(" {}", f) } else { "".to_owned() };
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    //
    let conn = connect_db(&dbname).await?;
    let sql = format!("alter table {} add{} foreign key({}) references {}({}){}", table_name, foreign_name, field, tab_name, tab_field, extra);
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn del_foreign_key(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;

    let constraint_name =  query::str(&body,"constraint_name")?;
    let sql  =  format!("alter table {} drop foreign key {}", table_name, constraint_name);
    //
    let conn = connect_db(&dbname).await?;
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn set_auto_increment(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;

    let start =  query::i32(&body,"start")?;
    let sql  =  format!("alter table {} drop auto_increment={}", table_name, start);
    //
    let conn = connect_db(&dbname).await?;
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    code::send_ok( conn.execute(stmt).await )
}
pub fn get_type(body:&JsonValue) -> Result<String, Json<JsonValue>>{
    let typ =  query::str(body,"Type")?;
    let x =  query::i16_op(body,"x")?;
    let y =  query::i16_op(body,"y")?;
    let mut s = "".to_owned();
    if let Some(x) = x {
        if let Some(y) = y {
            s = format!("({},{})", x, y)
        }else{
            s = format!("({})", x)
        }
    }
    //
    let typ = if typ.contains("_len") { 
        typ.replace("_len", &s)
     } else { 
        if x.is_some() { format!("{}{}", typ, s) } else { typ }
    };
    Ok(typ)
}
pub async fn mod_field(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    //
    let field =  query::str(&body,"Field")?;
	let typ = get_type(&body)?;
    let name =  query::str_op(&body,"name")?.unwrap_or_default();

    let nul = query::str(&body,"Null")?;
    let nul = if nul == "NO" {"not null"} else { "null"};

    let def = query::str_op(&body,"Default")?;
    let def  = if let Some(f) = def { format!("default {}", f) } else { "".to_owned() };

    let site =  query::str_op(&body,"site")?.unwrap_or_default();
    let site_name =  query::str_op(&body,"site_name")?.unwrap_or_default();
    let site = if site == "first" { site } else if site == "after" { format!("AFTER {}", site_name) } else { "".to_owned() };

    let extra =  query::str_op(&body,"Extra")?;
    let extra  = if let Some(f) = extra { format!(" {}", f) } else { "".to_owned() };
    let extra = if extra.contains("auto_increment") { extra.replace("auto_increment", "") } else { extra };

    let comment =  query::str_op(&body,"Comment")?;
    let comment  = if let Some(f) = comment { format!("COMMENT '{}'", f) } else { "".to_owned() };
    //
    let dbname = query::str(&body,"database")?;
    let table_name = query::str(&body,"table_name")?;
    let change = query::bool(&body,"change")?;
    let change = if change { "CHANGE" } else { "MODIFY COLUMN" };
    //
    let conn = connect_db(&dbname).await?;
    let sql = format!(
        "ALTER TABLE {} {} {} {} {} {} {} {} {}",
        table_name,
        change,
        field,
        name,
        typ,
        nul,
        def,
        extra,
        site
    );
    let stmt = Statement::from_string(DbBackend::MySql, sql);
    if comment.is_empty() {
        code::send_ok( conn.execute(stmt).await )
    }else {
        _ = conn.execute(stmt).await;
        let sql = format!(
            "ALTER TABLE {} MODIFY COLUMN {} {} {} {} {} {} {}",
            table_name,
            field,
            name,
            typ,
            nul,
            def,
            extra,
            comment
        );
        let stmt = Statement::from_string(DbBackend::MySql, sql);

        code::send_ok( conn.execute(stmt).await )
    }
    
}